import pandas as pd
import numpy as np
import random
# 第一步生成表格
products = ['A产品','B产品','*产品','C产品']
pro1 = [np.random.choice(products) for _ in range(66)]
s1 = pd.Series(np.random.randint(3500, 6000, size=66), index=pro1, name='一车间')
s2 = pd.Series(np.random.randint(3800, 6300, size=66), index=pro1, name='二车间')
s3 = pd.Series(np.random.randint(1900, 6800, size=66), index=pro1, name='三车间')
s4 = pd.Series(pro1, index=pro1, name='产品名称')
df1 = pd.DataFrame({s1.name: s1, s2.name: s2, s3.name: s3, s4.name: s4})
df1 = df1.set_index(['产品名称'])
s5 = pd.Series(np.random.randint(3500, 6200, size=66), index=pro1, name='一车间')
s6 = pd.Series(np.random.randint(3200, 6300, size=66), index=pro1, name='二车间')
s7 = pd.Series(np.random.randint(1900, 7000, size=66), index=pro1, name='三车间')
df2 = pd.DataFrame({s5.name: s5, s6.name: s6, s7.name: s7, s4.name: s4})
df2 = df2.set_index(['产品名称'])
writer = pd.ExcelWriter('D:/ITaudit/pandas_excel/pro1.xlsx')
print(df1)
df1.to_excel(writer,sheet_name='三车间产品表1')
print(df2)
df2.to_excel(writer,sheet_name='三车间产品表2')
writer.close()
print('done')